using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using HtmlAgilityPack;
using libUtil;

namespace libEOD
{
    public class WsjUST
    {
        DataTable m_tbBills = null;
        DataTable m_tbNotes = null;
        DateTime m_dtTableDate = DateTime.MinValue;
        public DataTable Bills { get { return m_tbBills; } }
        public DataTable Notes { get { return m_tbNotes; } }

        public string GetCurrentEOD()
        {
            try
            {
                string sURL = "http://online.wsj.com/mdc/public/page/2_3020-treasury.html?mod=mdc_bnd_pglnk";
                HtmlWeb hw = new HtmlWeb();
                //hw.Proxy = "http://gcm-ct-prod-pac.gcm.com:3128";
                //hw.Proxy = sProxy;
                HtmlDocument doc = hw.Load(sURL);
                
                if (!fnGetTableDate(doc))
                {
                    libUtil.Logger.Info("Cannot parse date from {0}", sURL);
                    return "FAIL";
                }
                libUtil.Logger.Info("Downloading EOD TSY for {0}", m_dtTableDate);

                HtmlNodeCollection tables = doc.DocumentNode.SelectNodes("//table");
                HtmlNodeCollection rows = tables[2].SelectNodes(".//tr");
                fnHandleNotes(rows);
                rows = tables[3].SelectNodes(".//tr");
                fnHandleBills(rows);
                return "OK";
            }
            catch (Exception e) {libUtil.Logger.Error("GetCurrentEOD FAIL with {0}", e.ToString());}
            return null;
        }

        public string GetEOD(DateTime dtTradeDate)
        {
            try
            {
                string sURL = "http://online.wsj.com/mdc/public/page/2_3020-treasury-{0:0000}{1:00}{2:00}.html?mod=mdc_pastcalendar";
                sURL = string.Format(sURL, dtTradeDate.Year, dtTradeDate.Month, dtTradeDate.Day);
                HtmlWeb hw = new HtmlWeb();
                //hw.Proxy = "http://gcm-ct-prod-pac.gcm.com:3128";
                //hw.Proxy = sProxy;
                HtmlDocument doc = hw.Load(sURL);
                if (!fnGetTableDate(doc)) return "TableDate Invalid";
                if (dtTradeDate != m_dtTableDate) return "TableDate != RequestDate";

                HtmlNodeCollection tables = doc.DocumentNode.SelectNodes("//table");
                HtmlNodeCollection rows = tables[2].SelectNodes(".//tr");
                fnHandleNotes(rows);
                rows = tables[3].SelectNodes(".//tr");
                fnHandleBills(rows);
                return "OK";
            }
            catch (Exception e)
            {
                //ErrorMail.This.Add(string.Format("ERROR: FX Parser Failed {0}}", e.ToString()));
            }
            return null;
        }

        public bool Save()
        {
            string sConnStr = string.Format("Data Source={0};Initial Catalog={1};integrated security=SSPI;",
                "SERGEY-LP\\SQLDEVLP", "Price");
            SqlConnection conn = new SqlConnection(sConnStr);
            SqlCommand cmd = new SqlCommand();
            conn.Open();
            cmd.Connection = conn;
            string sFMT= fnSQL();
            
            foreach(DataRow row in Notes.Rows)
            {
                DateTime dtMat = DateTime.Parse(row["Maturity"].ToString());
                double dCoupon = double.Parse(row["Coupon"].ToString());
                string sCusip = libRDS.LookUp.Me.Find(dCoupon, dtMat);
                cmd.CommandText = string.Format(sFMT, sCusip, "cusip", "WSJ", m_dtTableDate, 
                    row["Bid"], row["Asked"], row["AskedYield"]);
                cmd.ExecuteNonQuery();
            }
            cmd.Dispose();
            conn.Close();
            conn.Dispose();
            return true;
        }

        string fnSQL()
        {
            return "INSERT INTO [Price].[dbo].[Treasuries]" +
                           "([id]" +
                           ",[id_type]" +
                           ",[source]" +
                           ",[trade_date]" +
                           ",[bid_price]" +
                           ",[ask_price]" +
                           ",[ask_yield])" +
                     " VALUES" +
                           "('{0}'" +             //<id, varchar(50),>
                           ",'{1}'" +             //,<id_type, varchar(50),>
                           ",'{2}'" +             //,<source, varchar(50),>
                           ",'{3}'" +             //,<trade_date, datetime,>
                           ",{4}" +             //,<bid_price, float,>
                           ",{5}" +             //,<ask_price, float,>
                           ",{6})";             //,<ask_yield, float,>)
        }
        
        
        
        bool fnGetTableDate(HtmlDocument doc)
        {
            HtmlNodeCollection scr = doc.DocumentNode.SelectNodes("//script");
            foreach (HtmlNode n in scr)
            {
                string sText = n.InnerText.Trim();
                if (sText.StartsWith("var tableDate="))
                {
                    string[] ss = sText.Split('=');
                    string sDate = ss[1].Trim();
                    sDate = sDate.Replace("\"", "");
                    sDate = sDate.Replace(";", "");
                    m_dtTableDate = DateTime.Parse(sDate);
                    return true;
                }
            }
            return false;
        }
        int fnHandleNotes(HtmlNodeCollection rows)
        {
            m_tbNotes = new DataTable("Notes");
            m_tbNotes.Columns.Add("Maturity");
            m_tbNotes.Columns.Add("Coupon");
            m_tbNotes.Columns.Add("Bid");
            m_tbNotes.Columns.Add("Asked");
            m_tbNotes.Columns.Add("Chg");
            m_tbNotes.Columns.Add("AskedYield");
            bool bHeader = false;
            foreach (HtmlNode row in rows)
            {
                HtmlNodeCollection cols = row.SelectNodes(".//td");
                if (!bHeader && cols[0].InnerText.Trim() == "Maturity") { bHeader = true; continue; }
                if (bHeader)
                {
                    DataRow dr = m_tbNotes.NewRow();
                    dr["Maturity"] = fnDate(cols[0].InnerText.Trim());
                    dr["Coupon"] = double.Parse(cols[1].InnerText.Trim());
                    dr["Bid"] = fnPrice(cols[2].InnerText.Trim());
                    dr["Asked"] = fnPrice(cols[3].InnerText.Trim());
                    dr["Chg"] = fnChg(cols[4].InnerText.Trim());
                    dr["AskedYield"] = fnYield(cols[5].InnerText.Trim());
                    m_tbNotes.Rows.Add(dr);
                }
            }
            return m_tbNotes.Rows.Count;
        }
        int fnHandleBills(HtmlNodeCollection rows)
        {
            m_tbBills = new DataTable("Bills");
            m_tbBills.Columns.Add("Maturity");
            m_tbBills.Columns.Add("Bid");
            m_tbBills.Columns.Add("Asked");
            m_tbBills.Columns.Add("Chg");
            m_tbBills.Columns.Add("AskedYield");
            bool bHeader = false;
            foreach (HtmlNode row in rows)
            {
                HtmlNodeCollection cols = row.SelectNodes(".//td");
                if (!bHeader && cols[0].InnerText.Trim() == "Maturity") { bHeader = true; continue; }
                if (bHeader)
                {
                    DataRow dr = m_tbBills.NewRow();
                    dr["Maturity"] = fnDate(cols[0].InnerText.Trim());
                    dr["Bid"] = double.Parse(cols[1].InnerText.Trim());
                    dr["Asked"] = double.Parse(cols[2].InnerText.Trim());
                    dr["Chg"] = fnChg(cols[3].InnerText.Trim());
                    dr["AskedYield"] = fnYield(cols[4].InnerText.Trim());
                    m_tbBills.Rows.Add(dr);
                }
            }
            return m_tbBills.Rows.Count;
        }
        DateTime fnDate(string sDate)
        {
            string[] ssYMD = sDate.Split(' ');
            int nY = int.Parse(ssYMD[0].Trim());
            int nM = fnMonth(ssYMD[1].Trim());
            int nD = int.Parse(ssYMD[2].Trim());
            return new DateTime(nY, nM, nD);
        }
        double fnPrice(string sPx)
        {
            string[] ssIF = sPx.Split(':');
            int nI = int.Parse(ssIF[0].Trim());
            double dF = int.Parse(ssIF[1].Trim()) / 32.0;
            return nI + dF;
        }
        double fnChg(string sChg)
        {
            if (sChg.StartsWith("unch.")) return 0.0;
            else return double.Parse(sChg.Trim());
        }
        double fnYield(string sYield)
        {
            double dRet = 0;
            if (double.TryParse(sYield, out dRet)) return dRet;
            return 0;
        }
        int fnMonth(string sMonth)
        {
            switch (sMonth)
            {
                case "Jan": return 1;
                case "Feb": return 2;
                case "Mar": return 3;
                case "Apr": return 4;
                case "May": return 5;
                case "Jun": return 6;
                case "Jul": return 7;
                case "Aug": return 8;
                case "Sep": return 9;
                case "Oct": return 10;
                case "Nov": return 11;
                case "Dec": return 12;
                default: return -1;
            }
        }
    }
}
